3

多个字段唯一性如何处理?

  • 情景
有张表,表里有多个字段需要唯一,不能重复,否则就是重复数据,插不进去
  • 传统做法
直接给多个字段直接加唯一索引,简单粗暴
  • 现在做法
新增一个字段,这个字段加唯一索引,这样可以不用加过多唯一索引
insert into news(new_title, new_abstr, new_code) 
values('你是好','我是谁',MD5(CONCAT('你是好','我是谁')))

数据存在,就更新,不存在,就插入如何处理?

  • 情景
有张表,里面的记录不能存在重复记录,记录存在就更新,如果不存在就插入
  • 传统做法
先查询select,如果存在就update,不存在就insert
  • 现在做法
mysql专门有种写法,on DUPLICATE key Update
insert into news(new_title, new_abstr, new_code, update_time, create_time) 
values('你是好','我是谁',MD5(CONCAT('你是好','我是谁')))
on DUPLICATE key Update
update_time=now(), create_time=now()

查询结果的行号如何实现?

  • 情景
为某些功能的实现垫定基础
  • 做法
select name,age,sex,@rownum:=@rownum+1 as row_num 
from tb_student a,(select @rownum:=0) b 
order by age asc;

组内排序,并每组取出最高点击量的2个数据

  • 情景
经常会遇到2种情况
1、先按班级分组,并对各班学生按成绩由高到低排序(分组,并组内排序)
2、在第一种情形下,再取出每班成绩前10名
  • 做法
分组,并组内排序:
select name,class_name,score 
from tb_student order by class_name,score desc

取出每班成绩前10名:
SELECT w.name,w.score,w.row_num 
FROM
(
    SELECT g.name,g.score,IF(@bak=g.name,@row_num:=@row_num+1,@row_num:=1) AS row_num,@bak:=g.name 
    FROM
    (SELECT * 
    FROM course AS c,(SELECT @row_num:=0,@bak:='') AS b 
    ORDER BY c.`name`,c.score DESC
    ) AS g 
) as w 
WHERE w.row_num < 11

签到,并获取连续签到的用户列表

  • 情景
1、签到(同一天只能签到一次)
2、获取连续签到X天的用户列表
  • 传统做法
先查询,后更新(要使用到程序,2条SQl)
SELECT * FROM student_sign_log WHERE id = 7 AND sign_date = '2018-3-24'
update student set sign=sign+1
update student set sign=1
  • 现在做法
1、签到
UPDATE student 
SET sign_num = 
CASE
    WHEN sign_date < '2018-3-23' THEN 1
    WHEN sign_date = '2018-3-23' THEN sign_num+1
END,
sign_date = '2018-3-24'
WHERE id=7 AND sign_date < '2018-3-24'

2、获取连续签到X天的用户列表
SELECT w.name,w.score,w.row_num,w.sign_date
FROM
(
    SELECT g.name,g.score,IF(@bak=g.name and datediff(g.sign_date,@pre_date)=1,@row_num:=@row_num+1,@row_num:=1) AS row_num,@bak:=g.name,@pre_date:=g.sign_date
    FROM
    (SELECT * 
    FROM course AS c,(SELECT @row_num:=0,@bak:='',@pre_date:='') AS b 
    ORDER BY c.`name`,c.score DESC
    ) AS g 
) as w 
WHERE w.row_num < 11

根据商品分类,来获取最新日期的商品销售情况

  • 情景
1、获取每个商品分类的最新销售日期
2、根据上面查询的日期再次查询出最新商品销售情况
  • 做法
select a.* from pro_sale as a inner join
(select pro_class,max(sale_date) as sale_date from pro_sale group by pro_class) as b
on a.pro_class = b.pro_class AND a.sale_date = b.sale_date order by pro_class

商品按销量排序,并要把指定几个商品顶置最前面(排序作弊)

  • 情景
经常会遇到按条件排序之外还要把指定商品顶置,此时该如何处理呢?
  • 传统做法
(select * from product where id in (4,2,8) order by user_total desc)
union
(select * from product where id not in (4,2,8) order by user_total desc)
  • 现在做法
select * from product order by id in (4,2,8) desc,user_total desc

分组,如何统计组内数据名称?

  • 情景
获取所有分类下的所有商品名称统计
  • 做法
select GROUP_CONCAT(pro_name),pro_type from product group by pro_type

104828720
1.4k 声望222 粉丝

编程其实很枯燥,所以一定要有追求。